{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4180be2da9797114",
   "metadata": {},
   "source": [
    "### **数据处理练习作业**\n",
    "\n",
    "#### **目标：**\n",
    "\n",
    "通过本次练习，学生将学习如何使用Pandas进行数据加载、清洗、转换和透视表分析，从而熟悉实际数据处理流程，并提升对数据的洞察能力。\n",
    "\n",
    "#### **数据集描述：**\n",
    "\n",
    "数据集包含用户账户信息，主要字段如下：\n",
    "\n",
    "- 账户信息\n",
    "\n",
    "  :\n",
    "\n",
    "  - `date_account_created`：用户账户的创建日期。\n",
    "  - `timestamp_first_active`：用户首次活动的时间戳。\n",
    "  - `date_first_booking`：用户首次完成预订的日期。\n",
    "\n",
    "- 用户属性\n",
    "\n",
    "  :\n",
    "\n",
    "  - `gender`：用户的性别（可能存在未知值`-unknown-`）。\n",
    "  - `age`：用户的年龄。\n",
    "\n",
    "- 注册与渠道信息\n",
    "\n",
    "  :\n",
    "\n",
    "  - `signup_method`：用户的注册方式，例如`basic`、`facebook`等。\n",
    "  - `language`：用户注册时使用的语言。\n",
    "  - `affiliate_channel` 和 `affiliate_provider`：推广渠道及来源。\n",
    "\n",
    "- 设备与浏览器\n",
    "\n",
    "  :\n",
    "\n",
    "  - `first_device_type`：用户首次使用的设备类型。\n",
    "  - `first_browser`：用户首次使用的浏览器。\n",
    "\n",
    "- 目标变量\n",
    "\n",
    "  :\n",
    "\n",
    "  - `country_destination`：用户目标国家。\n",
    "\n",
    "#### **作业任务：**\n",
    "\n",
    "### **第一部分：数据预处理**\n",
    "\n",
    "1. **数据加载**:\n",
    "   - 使用Pandas将数据集加载为DataFrame，查看数据的基本信息（例如`df.info()`和`df.head()`）。\n",
    "2. **处理缺失值**:\n",
    "   - 将数据中值为`-unknown-`的项替换为`NaN`。\n",
    "   - 检查每列的缺失值情况（例如使用`df.isnull().sum()`）。\n",
    "   - 选择合适的填充方式处理缺失值：\n",
    "     - 对`age`列的缺失值，使用平均值或中位数填充，或将年龄设为合理范围（例如`0-100岁`）。\n",
    "     - 对其他列的缺失值，可根据实际情况选择填充或删除。\n",
    "3. **时间字段处理**:\n",
    "   - 将`timestamp_first_active`转换为标准的日期时间格式。\n",
    "   - 通过计算`date_account_created`和`timestamp_first_active`之间的时间差（以天为单位），新增一列`days_difference`。\n",
    "4. **字段优化与清理**:\n",
    "   - 删除明显无关的列（如`date_first_booking`，如果数据缺失率过高或与分析无关）。\n",
    "   - 统一数据格式，如将`language`列中非小写值转换为小写。\n",
    "\n",
    "------\n",
    "\n",
    "### **第二部分：数据转换**\n",
    "\n",
    "1. **新增列 - 年龄分组**:\n",
    "\n",
    "   - 根据\n",
    "\n",
    "     ```\n",
    "     age\n",
    "     ```\n",
    "\n",
    "     创建一个新的分类列\n",
    "\n",
    "     ```\n",
    "     age_group\n",
    "     ```\n",
    "\n",
    "     ：\n",
    "\n",
    "     - `0-18岁`：`age <= 18`\n",
    "     - `19-30岁`：`19 <= age <= 30`\n",
    "     - `31-45岁`：`31 <= age <= 45`\n",
    "     - `46岁及以上`：`age > 45`\n",
    "\n",
    "2. **新增列 - 活跃年**:\n",
    "\n",
    "   - 根据`timestamp_first_active`提取用户首次活动的年份，创建一列`active_year`。\n",
    "\n",
    "3. **分类变量处理**:\n",
    "\n",
    "   - 对`gender`、`signup_method`等分类变量进行值频率统计，初步了解各类值的分布情况。\n",
    "\n",
    "------\n",
    "\n",
    "### **第三部分：数据透视表分析**\n",
    "\n",
    "以下是透视表分析任务的详细要求：\n",
    "\n",
    "1. **用户平均年龄分析**:\n",
    "\n",
    "   - 创建一个透视表，展示按`gender`（性别）和`country_destination`（目标国家）分组的用户平均年龄。\n",
    "\n",
    "   示例列：\n",
    "\n",
    "   ```\n",
    "   gender | country_destination | average_age\n",
    "   ```\n",
    "\n",
    "2. **注册方式与设备类型分布**:\n",
    "\n",
    "   - 创建一个透视表，统计按`signup_method`（注册方式）和`first_device_type`（首次设备类型）分组的用户数量。\n",
    "\n",
    "   示例列：\n",
    "\n",
    "   ```\n",
    "   signup_method | first_device_type | user_count\n",
    "   ```\n",
    "\n",
    "3. **推广渠道与目标国家关系**:\n",
    "\n",
    "   - 创建一个透视表，展示不同`affiliate_channel`（推广渠道）和`country_destination`（目标国家）之间的用户数量分布。\n",
    "\n",
    "   示例列：\n",
    "\n",
    "   ```\n",
    "   affiliate_channel | country_destination | user_count\n",
    "   ```\n",
    "\n",
    "4. **设备类型和浏览器组合分析**:\n",
    "\n",
    "   - 创建一个透视表，按`first_device_type`和`first_browser`统计用户数量，查看设备和浏览器的主要组合情况。\n",
    "\n",
    "   示例列：\n",
    "\n",
    "   ```\n",
    "   first_device_type | first_browser | user_count\n",
    "   ```\n",
    "\n",
    "5. **注册时间与活跃时间关系**:\n",
    "\n",
    "   - 创建一个透视表，按`active_year`和`date_account_created`分组，统计用户数量，分析不同年份的用户活跃和注册趋势。\n",
    "\n",
    "------\n",
    "\n",
    "#### **提交要求**:\n",
    "\n",
    "- 提交Python脚本或Jupyter Notebook，确保代码注释清晰，并包含关键输出结果。\n",
    "- 提交1-2段分析报告，总结透视表中的发现，例如：\n",
    "  - 用户的目标国家与年龄、性别分布之间的关系。\n",
    "  - 不同注册方式和设备类型的用户分布模式。\n",
    "\n",
    "#### **提示**:\n",
    "\n",
    "- 请多使用Pandas自带的函数（如`pivot_table`、`groupby`等）完成任务。\n",
    "- 所有任务代码需运行正确，结果需合理，输出结果需清晰展示。\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "225596363946aa47",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-11-25T13:17:39.170658Z",
     "start_time": "2024-11-25T13:17:39.169472Z"
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
